"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : Trigger
Case Name   : MOT不支持触发器
Create At   : 2021/1/19
Owner       : opentestcase001
Description :
    1、修改enable_incremental_checkpoint=off
    2、重启数据库,gs_om -t stop && gs_om -t start
    3、创建内存表外表&源表,创建触发器函数,创建触发器；
    4、清理数据;
Expect      :
    1、修改成功；
    2、重启数据库成功；
    3、创建表成功,创建触发器函数成功,创建触发器失败；
    4、清理环境成功；
History     :
    Modified by opentestcase004 2023/2/10:设置参数enable_incremental_checkpoint未在备
    机同步设置,导致备机在enable_incremental_checkpoint为on时无法创建mot表 & 优
    化格式
"""
import os
import unittest
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common

LOG = Logger()
CONSTANT = Constant()
Primary_SH = CommonSH('PrimaryDbUser')


class TriggerMotTest(unittest.TestCase):
    def setUp(self):
        LOG.info("-----------this is setup-----------")
        LOG.info(f"-----{os.path.basename(__file__)} start-----")
        self.common = Common()
        self.tb_name1 = 't_function_trigger_0055_01'
        self.tb_name2 = 't_function_trigger_0055_02'
        self.function = 'f_function_trigger_0055'
        self.trigger = 'tr_function_trigger_0055'

    def test_trigger_mot(self):
        LOG.info("======检查参数,修改配置,并重启数据库======")
        self.config_item = 'enable_incremental_checkpoint=off'
        self.enable_incremental_checkpoint = self.common.show_param(
            'enable_incremental_checkpoint')
        if self.enable_incremental_checkpoint != 'off':
            Primary_SH.execute_gsguc('set', CONSTANT.GSGUC_SUCCESS_MSG,
                                     self.config_item)
            result = Primary_SH.restart_db_cluster()
            LOG.info(result)
            self.assertTrue(result)

        LOG.info("=======步骤1:创建内存表 外表&源表=======")
        sql_cmd1 = f'drop foreign table if exists {self.tb_name1};' \
                   f'drop foreign table if exists {self.tb_name2};' \
                   f'create foreign table {self.tb_name1}(id1 int,id2 int,' \
                   f'id3 int);create foreign table {self.tb_name2}(id1 int,' \
                   f'id2 int,id3 int);'
        LOG.info(sql_cmd1)
        create_res1 = Primary_SH.execut_db_sql(sql_cmd1)
        LOG.info(create_res1)
        self.assertEqual(create_res1.count(
            CONSTANT.DROP_FOREIGN_SUCCESS_MSG), 2, '执行失败')
        self.assertEqual(create_res1.count(
            CONSTANT.CREATE_FOREIGN_SUCCESS_MSG), 2, '执行失败')

        LOG.info("======步骤2:创建触发器函数 & 触发器======")
        create_cmd2 = f'''
            create or replace function {self.function}() returns trigger
            as
            \$\$
            declare
            begin
            insert into {self.tb_name2} values(NEW.id1,NEW.id2,NEW.id3);
            return NEW;
            end
            \$\$ language plpgsql;
            create trigger {self.trigger} before insert on
            {self.tb_name1} for each row execute procedure {self.function}();
            '''
        LOG.info(create_cmd2)
        create_res2 = Primary_SH.execut_db_sql(create_cmd2)
        LOG.info(create_res2)
        self.assertIn('not a table or view', create_res2, '执行失败')

    def tearDown(self):
        LOG.info("======清理环境,恢复配置======")
        del_cmd = f'drop foreign table {self.tb_name1};' \
                  f'drop foreign table {self.tb_name2};' \
                  f'drop function {self.function}();'
        LOG.info(del_cmd)
        del_msg = Primary_SH.execut_db_sql(del_cmd)
        LOG.info(del_msg)
        result1 = Primary_SH.execute_gsguc(
            'set', CONSTANT.GSGUC_SUCCESS_MSG,
            f'enable_incremental_checkpoint='
            f'{self.enable_incremental_checkpoint}')
        LOG.info(result1)
        self.assertEqual(del_msg.count(
            CONSTANT.DROP_FOREIGN_SUCCESS_MSG), 2, '执行失败')
        self.assertIn(CONSTANT.DROP_FUNCTION_SUCCESS_MSG, del_msg, '执行失败')
        self.assertTrue(result1)
        LOG.info(f"-----{os.path.basename(__file__)} end-----")
